package com.ytjj.qmyx.admin.service.impl;

import com.alibaba.fastjson.JSON;
import com.alipay.api.domain.ExtraInfo;
import com.aliyun.oss.OSSClient;
import com.github.pagehelper.PageHelper;
import com.ytjj.common.api.CommonPage;
import com.ytjj.common.constants.DateUtils;
import com.ytjj.common.constants.RedisConstants;
import com.ytjj.common.exception.ApiException;
import com.ytjj.common.service.RedisService;
import com.ytjj.common.utils.AliyunOSSClientUtil;
import com.ytjj.common.utils.DateTimeUtil;
import com.ytjj.qmyx.admin.constants.RedisConstant;
import com.ytjj.qmyx.admin.dao.KnowledgeBaseDao;
import com.ytjj.qmyx.admin.mapper.AdminMapper;
import com.ytjj.qmyx.admin.model.Admin;
import com.ytjj.qmyx.admin.model.KnowledgeBaseEntity;
import com.ytjj.qmyx.admin.model.dto.KnowledgeBaseExcelEntity;
import com.ytjj.qmyx.admin.model.request.KnowledgeBaseRequest;
import com.ytjj.qmyx.admin.model.request.KnowledgeImportResponse;
import com.ytjj.qmyx.admin.service.KnowledgeBaseService;
import com.ytjj.qmyx.admin.service.MemberService;
import com.ytjj.qmyx.admin.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeansException;
import org.springframework.data.redis.core.RedisHash;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collector;
import java.util.stream.Collectors;

/**
 * @author jiawei
 * @date 2024年08月23日 11:19
 * @describe
 */
@Service
@Slf4j
public class KnowledgeBaseServiceImpl implements KnowledgeBaseService {


    @Resource
    private KnowledgeBaseDao knowledgeBaseDao;
    @Resource
    private MemberService memberService;
    @Resource
    private RedisService redisService;

    /**
     * 列头名称
     */
    private static final List<String> TITLE_LIST = Arrays.asList("日期","姓名","供应链商品ID","所属商城","商品名称","客户问题","相关回答","图片1","图片2","图片3","图片4");
    /**
     * 标题行锁占用的行数
     */
    private static final int TITLE_NUM = 1;


    @Override
    public Map<String,Integer> saveExcelData(MultipartFile file) {
        Map<String,Integer> infoMap = new HashMap<>();
        infoMap.put("success",0);
        infoMap.put("fail",0);
        // 读取上传excel
        Workbook wb = readExcel(file);
        //Workbook wb = WorkbookFactory.create(file.getInputStream());
        if (wb == null) {
            return null;
        }
        // 获取当前页，这里如果有多页时可以获取更多的页，通过循环获取每页的数据
        // 获取总页数：wb.getNumberOfSheets(),获取某一个：wb.getSheet(sheetName)
        Sheet sheet = wb.getSheetAt(0);
        boolean result = checkTitleName(sheet);
        if (!result) {
            throw new ApiException("导入表格与规定模板不一致！");
        }
        //1:获取图片集合。行-列为key
        Map<String, PictureData> pictureDataMap = readExcelPicture(sheet);
        //2:解析excel中的数据（包括图片信息）
        List<KnowledgeBaseExcelEntity> knowledgeBaseExcelEntities = readExcelInfoAndPutList(sheet, TITLE_NUM, pictureDataMap);
        KnowledgeImportResponse knowledgeImportResponse = new KnowledgeImportResponse();
        List<KnowledgeImportResponse.ExtraInfo> importFails = new ArrayList<>();
        if (!CollectionUtils.isEmpty(knowledgeBaseExcelEntities)) {
            // 初始化OssClient
            OSSClient ossClient = AliyunOSSClientUtil.getOSSClient();
            for (KnowledgeBaseExcelEntity entity : knowledgeBaseExcelEntities) {
                    KnowledgeBaseEntity knowledgeBaseEntity = new KnowledgeBaseEntity();
                    BeanUtils.copyProperties(entity,knowledgeBaseEntity);
                    List<String> picUrls = new ArrayList<>();
                    for (PictureData pictureData : entity.getPictureData()) {
                        byte[] data = pictureData.getData();
                        pictureData.getMimeType();
                        pictureData.getPictureType();
                        String fileExtension = pictureData.suggestFileExtension();
                        String url = AliyunOSSClientUtil.uploadImgToOSS(ossClient, new ByteArrayInputStream(data), fileExtension);
                        if(!StringUtils.isEmpty(url)){
                            picUrls.add(url);
                        }
                    }
                    if(!CollectionUtils.isEmpty(picUrls)){
                        knowledgeBaseEntity.setAnswerPictures(String.join(",",picUrls));
                    }
                    //校验数据是否已存在
                    Integer exist = knowledgeBaseDao.isExist(entity.getSupplyProductId(), entity.getQuestion(), entity.getAnswer());
                    if(null != exist && exist != 0){
                        infoMap.put("fail",infoMap.get("fail")+1);
                        continue;
                    }
                try {
                    int i = knowledgeBaseDao.insertEntity(knowledgeBaseEntity);
                    if (i > 0) {
                        log.info("已导入数据：{}", knowledgeBaseEntity.getQuestion());
                        infoMap.put("success", infoMap.get("success") + 1);
                    }
                } catch (Exception e) {
                    KnowledgeImportResponse.ExtraInfo extraInfo = new KnowledgeImportResponse().new ExtraInfo();
                    extraInfo.setSupplyProductId(knowledgeBaseEntity.getSupplyProductId());
                    extraInfo.setQuestion(knowledgeBaseEntity.getQuestion());
                    extraInfo.setAnswer(knowledgeBaseEntity.getAnswer());
                    importFails.add(extraInfo);
                    log.info("插入数据出错：{}", e.getMessage());
                    infoMap.put("fail",infoMap.get("fail")+1);
                }
            }
        }
        knowledgeImportResponse.setImportSuccessTotal(infoMap.get("success"));
        knowledgeImportResponse.setImportSuccessTotal(infoMap.get("fail"));
        knowledgeImportResponse.setImportTime(new Date());
        if(!CollectionUtils.isEmpty(importFails)){
            knowledgeImportResponse.setImportFails(importFails);
            redisService.set(RedisConstants.RETURN_GOOD_IMPORT_HISTORY,JSON.toJSONString(knowledgeImportResponse));
        }
        return infoMap;
    }

    /**
     * 查询最近导入的记录
     */
    @Override
    public KnowledgeImportResponse getRecentImportRecords() {
        String result = redisService.get(RedisConstants.RETURN_GOOD_IMPORT_HISTORY);
        KnowledgeImportResponse knowledgeImportResponse = JSON.parseObject(result, KnowledgeImportResponse.class);
        return knowledgeImportResponse;
    }


    @Override
    public CommonPage<KnowledgeBaseEntity> searchKnowledgeBase(KnowledgeBaseRequest request) {
        PageHelper.startPage(request.getPageNum(), request.getPageSize());
        List<KnowledgeBaseEntity> knowledgeBaseEntities = knowledgeBaseDao.searchKnowledgeBase(request);
        return CommonPage.restPage(knowledgeBaseEntities);
    }

    @Override
    public Integer batchDelItem(List<Integer> ids) {
        if(CollectionUtils.isEmpty(ids)){
            throw new ApiException("请选择要删除的数据！");
        }
        int i = knowledgeBaseDao.batchDelItem(ids);
        return i;
    }

    @Override
    public Integer addOrEdit(KnowledgeBaseEntity entity) {
        Integer result = null;
        Admin admin = memberService.getAdmin();
        //修改
        if (null != entity.getId()) {
            entity.setUpdateTime(new Date());
            entity.setUpdateName(admin.getName());
            result = knowledgeBaseDao.updateEntity(entity);
        } else {//新增
            entity.setRecordTime(new Date());
            entity.setRecordName(admin.getName());
            result = knowledgeBaseDao.insertEntity(entity);
        }
        return result;
    }

    /**
     * @description: 获取图片集合
     * @param: [file, sheet]
     * @return: java.util.Map<java.lang.String, org.apache.poi.ss.usermodel.PictureData>
     * @date: 2023/11/7 17:30
     * @version: 1.0
     **/
    private Map<String, PictureData> readExcelPicture(Sheet sheet) {
        // 声明当前页图片的集合
        Map<String, PictureData> sheetImageMap;
        // 获取图片
        try {
            //2003版本的excel，用.xls结尾
            sheetImageMap = getPicturesHSS((HSSFSheet) sheet);
        } catch (Exception ex) {
            try {
                //2007版本的excel，用.xlsx结尾
                sheetImageMap = getPicturesXSS((XSSFSheet) sheet);
            } catch (Exception e) {
                log.error(ex.getMessage());
                throw new ApiException("解析图片异常！");
            }
        }
        return sheetImageMap;
    }

    /**
     * 获取图片和位置 (xlsx)
     * @param sheet
     * @return
     * @throws IOException
     */
    private Map<String, PictureData> getPicturesXSS(XSSFSheet sheet) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    //解决图片空指针报错问题
                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                    //下面这个处理时间较长
                    //XSSFClientAnchor anchor = pic.getPreferredSize();
                    //CTMarker marker = anchor.getFrom();
                    // 行号-列号，该取用方式列准确率不高
                    //String key = marker.getRow() + "-" + marker.getCol();
                    //行号-列号
                    String key = anchor.getRow1() + "-" + (anchor.getCol2());
                    sheetIndexPicMap.put(key, pic.getPictureData());
                }
            }
        }
        return sheetIndexPicMap;
    }

    /**
     * 获取图片和位置 (xls)
     * @param sheet
     * @return
     * @throws IOException
     */
    private Map<String, PictureData> getPicturesHSS(HSSFSheet sheet) {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol2();
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 读取excel
     * @param file
     * @return
     */
    private Workbook readExcel(MultipartFile file) {
        Workbook wb = null;
        ZipSecureFile.setMinInflateRatio(0);
        if (file == null) {
            return null;
        }
        InputStream is;
        try {
            is = file.getInputStream();
            //2003版本的excel，用.xls结尾
            //得到工作簿
            wb = new HSSFWorkbook(is);
        } catch (Exception ex) {
            log.error(ex.getMessage());
            try {
                //2007版本的excel，用.xlsx结尾
                is = file.getInputStream();
                //得到工作簿
                wb = new XSSFWorkbook(is);
            } catch (IOException e) {
                log.error(ex.getMessage());
            }
        }
        return wb;
    }

    /**
     * 解析Excel的内容和图片
     **/
    private List<KnowledgeBaseExcelEntity> readExcelInfoAndPutList(Sheet sheet, int titleNum, Map<String, PictureData> pictureDataMap){
        //存储实体list
        List<KnowledgeBaseExcelEntity> list = new ArrayList<>();
        // 获取不为空的总行数
        int rowSize = sheet.getPhysicalNumberOfRows();
        // 跳过标题行，开始遍历每一行，获取除了图片信息外的字段信息
        for (int rowNum = titleNum; rowNum < rowSize; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                // 处理该行
//                int cellSize = row.getPhysicalNumberOfCells();
                KnowledgeBaseExcelEntity entity = new KnowledgeBaseExcelEntity();
                List<PictureData> pictureDatas = new ArrayList<>();
                entity.setRecordTime(parseDateWithMultipleFormats(dealCellValue(row.getCell(0))));
                entity.setRecordName(dealCellValue(row.getCell(1)));
                //设置供应链商品id
                String supplyProductIdStr = dealCellValue(row.getCell(2));
                if (!StringUtils.isEmpty(supplyProductIdStr) && supplyProductIdStr.matches("\\d+")) {
                    entity.setSupplyProductId(Integer.valueOf(supplyProductIdStr));
                }
                entity.setProductName(dealCellValue(row.getCell(4)));
                entity.setQuestion(dealCellValue(row.getCell(5)));
                entity.setAnswer(dealCellValue(row.getCell(6)));
                //处理图片
                for (int j = 6; j < TITLE_LIST.size(); j++) {
                    if (pictureDataMap != null && pictureDataMap.size() > 0) {
                        // 获取图片信息
                        String key = rowNum + "-" + (j);
                        PictureData pictureData = pictureDataMap.get(key);
                        if (pictureData != null) {
                            pictureDatas.add(pictureData);
                        }
                    }
                }
                entity.setPictureData(pictureDatas);
                list.add(entity);
            }
        }
        return list;
    }

    /**
     * 处理单元格数据
     */
    public String dealCellValue(Cell cell){
        String cellValue = "";
        if (cell != null && cell.getCellType() != CellType.BLANK.getCode()) {
            //是否是日期格式
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMMM-yyyy");
                // 定义日期格式
                SimpleDateFormat dateStringFormat = new SimpleDateFormat("yyyy/MM/dd");
                try {
                    Date parse = dateFormat.parse(cell.toString());
                    cellValue = dateStringFormat.format(parse);
                } catch (ParseException e) {
                    log.error("知识库excel导入，日期时间转换出错！{}", e);
                }
            } else {
                // 使用 DataFormatter 获取单元格的值
                DataFormatter formatter = new DataFormatter();
                cellValue = formatter.formatCellValue(cell);
            }
        }
        return cellValue;
    }

    /**
     * 校验Excel列头是否符合模板要求
     * @param: sheet
     * @param: titleNum 表头所占用的行数
     **/
    private boolean checkTitleName(Sheet sheet) {
        //表头的在poi实际的下表索引
        int titleIndex = TITLE_NUM-1;
        boolean result = true;

        //获取表头行
        Row titleRow = sheet.getRow(titleIndex);
        //获取表头的总列数
        int columnSize = sheet.getRow(titleIndex).getPhysicalNumberOfCells();
        // 遍历一行中每列值
        for (int cellNum = 0; cellNum < columnSize; cellNum++) {
            Cell cell = titleRow.getCell(cellNum);
            if (cell != null && cell.getCellType() != CellType.BLANK.getCode()) {
                // 使用 DataFormatter 获取单元格的值
                DataFormatter formatter = new DataFormatter();
                String cellValue = formatter.formatCellValue(cell);
                if(!TITLE_LIST.get(cellNum).equals(cellValue)){
                    result = false;
                    break;
                }
            }
        }
        return result;
    }

    public static void main(String[] args) {
        String dateString = "08-四月-2023";
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMMM-yyyy");
        try {
            Date date = dateFormat.parse(dateString);
            System.out.println("Converted date: " + date);
            // 定义日期格式
            SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/MM/dd");
            // 将Date对象转换为字符串
            String dateString2 = dateFormat2.format(date);
            // 输出结果
            System.out.println("字符串日期dateString2: " + dateString2);
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    /**
     * 字符串日期转时间格式
     */
    private static Date parseDateWithMultipleFormats(String dateString) {
        Date date = null;
        List<String> formats = Arrays.asList("yyyy/MM/dd", "yyyy.MM.dd");
        for (String format : formats) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                // 设置严格模式，确保日期有效
                sdf.setLenient(false);
                date = sdf.parse(dateString);
            } catch (Exception e) {
//                log.info("当前格式不匹配！");
                // 如果当前格式无法解析，则尝试下一个格式
            }
        }

        return date; // 如果所有格式都无法解析，则返回null
    }

}
